package test.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class OracleJDBC {

	/*
	 * 数据库连接信息
	 */
	String driverName = "oracle.jdbc.OracleDriver";
	String dbURL = "jdbc:oracle:thin:@192.168.2.110:1521:huainyj";
	String userName = "db_yjpt_yjxt";
	String userPwd="css";
	
	/*
	 * 通用实例
	 */
	Connection dbConn = null;
	Statement stat = null;
	ResultSet rs = null;
	
	/**
	 * 连接数据库
	 */
	@Before
	public void befor(){
		
		try
		{
			Class.forName(driverName);
			System.out.println("加载驱动成功！");
		}catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("加载驱动失败！");
		}
		try
		{
			 dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
			System.out.println("连接数据库成功！");
		}catch(Exception e)
		{
			e.printStackTrace();
			System.out.print("SQL Server连接失败！");
		}
		
		try {
			stat = dbConn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("创建语句对象接口失败！");
		}

	}
	
	
	/**
	 * 查询示例，查询emp表
	 */
	@Test
	public void query(){
		
		String sql = "select * from emp;";
		try {
			rs = stat.executeQuery(sql);
			while(rs.next()){
				
				System.out.println("id:"+rs.getString("id"));
				System.out.println("name:"+rs.getString("name"));
				System.out.println("address:"+rs.getString("address"));
				System.out.println("---------------------------------------------------");
			}
			System.out.println("查询完成！");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("结果集操作失败！");
		}
		
	}
	
	
	/**
	 * 新增示例
	 */
	@Test
	public void add(){
		String sql = "insert into emp values('8','john','earth');";
		try {
			int count = stat.executeUpdate(sql);
			System.out.println("数据插入完成:"+count+"条数据受影响！");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("插入数据失败！");
		} 
	}
	
	/**
	 * 更新示例
	 */
	@Test
	public void update(){
		String sql = "update emp set address='earth' where id = '1'";
		try {
			int count = stat.executeUpdate(sql);
			System.out.println("数据更新完成:"+count+"条数据受影响！");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("更新数据失败！");
		} 
	}
	
	/**
	 * 删除示例
	 */
	@Test
	public void detele(){
		String sql = "delete from emp where id='2'";
		try {
			int count = stat.executeUpdate(sql);
			System.out.println("数据删除完成:"+count+"条数据受影响！");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("删除数据失败！");
		} 
	}
	
	@After
	public void after(){
		if(dbConn != null){
			try {
				dbConn.close();
				System.out.println("数据库连接已断开！");
			} catch (SQLException e) {
				System.out.println("SQL Server关闭连接失败！");
				e.printStackTrace();
			}
		}
	}
	

}
